package job2code;

import job2code.entity.Emp;
import job2code.util.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Objects;

//PrepareStatement 测试类
//忽略sql检查警告
@SuppressWarnings("SqlResolve")
public class JDBC_PrepareStatement {
    public static void main(String[] args) throws Exception {
//        从连接池获取连接
        Connection connection = JDBCUtils.getConnection();

//        1.根据指定姓名查询数据,忽略connection为null的情况
        PreparedStatement prepareStatement = Objects.requireNonNull(connection)
//                预编译sql，防注入
                .prepareStatement("select * from employee where name = ?");
//        设置参数
        prepareStatement.setString(1,"林黛玉");
//        执行查询
        ResultSet resultSet = prepareStatement.executeQuery();
        System.out.println("查询林黛玉的员工信息为");
//        显示查询结果
        show(resultSet);
//        按参数顺序关闭，符合后开先关原则
        JDBCUtils.close(resultSet, prepareStatement);

//        2. 查询所有性别为男的员工信息
        prepareStatement = Objects.requireNonNull(connection)
//                预编译sql，防注入
                .prepareStatement("select * from employee where gender = ?");
        //        设置参数
        prepareStatement.setString(1,"男");
//        执行查询
        resultSet = prepareStatement.executeQuery();
        System.out.println("所有性别为男的员工信息为");
//        显示查询结果
        show(resultSet);
//        按参数顺序关闭，符合后开先关原则
        JDBCUtils.close(resultSet, prepareStatement, connection);
    }
//   展示结果集。
    private static void show(ResultSet resultSet) throws SQLException {
        while (resultSet.next()) {
            Emp emp = new Emp(resultSet.getInt("id"),
                    resultSet.getString("name"),
                    resultSet.getString("gender"),
                    resultSet.getDouble("salary"),
                    resultSet.getDouble("bonus"),
                    resultSet.getDate("join_date"));
            System.out.println(emp);
        }
    }
}
/*

insert into employee values (1, '孙悟空', '男', 7200, 720, '2013-02-04');
insert into employee values (2, '猪八戒', '男', 3600, 360, '2010-12-02');
insert into employee values (3, '唐僧', '男', 9000, 900, '2008-08-08');
insert into employee values (4, '白骨精', '女', 5000, 500, '2015-10-07');
insert into employee values (5, '蜘蛛精', '女', 5000, 500, '2011-03-14');
insert into employee values (6, '玉兔精', '女', 200, 20, '2000-03-14');
insert into employee values (7, '林黛玉', '女', 10000, 1000, '2019-10-07');
insert into employee values (8, '黄蓉', '女', 3500, 350, '2011-09-14');
insert into employee values (9, '吴承恩', '男', 20000, 2000, '2000-03-14');
insert into employee values (10, '孙悟饭', '男', 10, 1, '2020-03-14');
insert into employee values (11, '兔八哥', '女', 300, 30, '2010-03-14');

 */